/**
 * Created by wry on 2015/4/13.
 */
var Users = mongoose.model('user');
var async = require('async');
var neighbourActivity = mongoose.model('activity_neighbour');
var mysqlUtil = require('../../common/utils/mysqlUtil');
var Question = mongoose.model('question');
var Community = mongoose.model('community');

Date.prototype.format = function (format) {
    var o = {
        "M+": this.getMonth() + 1, //month
        "d+": this.getDate(), //day
        "h+": this.getHours(), //hour
        "m+": this.getMinutes(), //minute
        "s+": this.getSeconds(), //second
        "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
        "S": this.getMilliseconds() //millisecond
    }
    if (/(y+)/.test(format)) format = format.replace(RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o) if (new RegExp("(" + k + ")").test(format))
        format = format.replace(RegExp.$1,
            RegExp.$1.length == 1 ? o[k] :
                ("00" + o[k]).substr(("" + o[k]).length));
    return format;
}

if (!String.prototype.format) {
    String.prototype.format = function() {
        var args = arguments;
        return this.replace(/{(\d+)}/g, function(match, number) {
            return typeof args[number] != 'undefined'
                ? args[number]
                : match
                ;
        });
    };
}

function commentToString(f) {
    return f.toString().
        replace(/^[^\/]+\/\*!?/, '').
        replace(/\*\/[^\/]+$/, '');
}


function sqlConditionString(conditionList){
    var len = conditionList.length
    if(len == 0)
        return ''
    var condition = "where "

    for(var i = 0; i < len; i++){
        condition += conditionList[i]
        if(i != len - 1)
            condition += ' and '
    }
    return condition
}


exports.getBackOfficeCommunityMainStatsService = function (community, callback) {

    var commCondition = ''
    if(community != 'all')
        commCondition += 'and community="' + community + '"'
    else
        commCondition += 'and community is not null'

    async.series([
        function (callbackInner){       // 总用户数
            var sql = commentToString(function(){
                /*
                 select count(distinct _id) count, convert(date_add(adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day), interval 6 day), char) weekEnding
                 from 66xiaoqu_dwh.users
                 where (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%' or mobilePhone is null)
                 {0}
                 group by adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day);
                 */
            }).toString().format(commCondition)
            mysqlUtil.query(sql, function(err, result){
                callbackInner(err, result)
            })
        },
        //function (callbackInner){   // 总V用户数
        //    var authCondition = 'and authenticationStatus = "true"'
        //    var sql = commentToString(function(){
        //        /*
        //         select count(distinct _id) count, convert(date_add(adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day), interval 6 day), char) weekEnding
        //         from 66xiaoqu_dwh.users
        //         where (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%' or mobilePhone is null)
        //         {0}
        //         group by adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day);
        //         */
        //    }).toString().format(commCondition, authCondition)
        //
        //    mysqlUtil.query(sql, function(err, result){
        //        callbackInner(err, result)
        //    })
        //},
        function (callbackInner){   // 发布数
            var sql = commentToString(function(){
                /*
                 select count(*) count, convert(date_add(adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day), interval 6 day), char) weekEnding
                 from 66xiaoqu_dwh.question
                 {0}
                 group by adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day)
                 */
            }).toString().format(commCondition.replace("and", "where"))
            mysqlUtil.query(sql, function(err, result){
                callbackInner(err, result)
            })
        },
        function (callbackInner){   // 发布人数
            var sql = commentToString(function(){
                /*
                 select count(*) count, convert(date_add(adddate(date(dateCreated), INTERVAL - WEEKDAY(dateCreated) day), interval 6 day), char) weekEnding
                 from
                 (select * from 66xiaoqu_dwh.question
                 {0}
                 group by createdBy) as d
                 group by adddate(date(d.dateCreated), INTERVAL - WEEKDAY(d.dateCreated) day);
                 */
            }).toString().format(commCondition.replace("and", "where"))
            mysqlUtil.query(sql, function(err, result){
                callbackInner(err, result)
            })
        },
        function (callbackInner){   // 回复数
            var sql = commentToString(function(){
                /*
                 select count(*) count, convert(date_add(adddate(date(dateReplied), INTERVAL - WEEKDAY(dateReplied) day), interval 6 day), char) weekEnding
                 from 66xiaoqu_dwh.reply r, 66xiaoqu_dwh.question q
                 where r._Questionid = q._id
                 {0}
                 group by adddate(date(dateReplied), INTERVAL - WEEKDAY(dateReplied) day)
                 */
            }).toString().format(commCondition)
            mysqlUtil.query(sql, function(err, result){
                callbackInner(err, result)
            })
        },
        function (callbackInner){   // 回复人数
            var sql = commentToString(function(){
                /*
                 select count(*) count, convert(date_add(adddate(date(dateReplied), INTERVAL - WEEKDAY(dateReplied) day), interval 6 day), char) weekEnding
                 from
                 (select r.* from
                 66xiaoqu_dwh.reply r, 66xiaoqu_dwh.question q
                 where r._Questionid = q._id
                 {0}
                 group by r.repliedBy
                 ) as rr
                 group by adddate(date(dateReplied), INTERVAL - WEEKDAY(dateReplied) day)
                 ;
                 */
            }).toString().format(commCondition)
            mysqlUtil.query(sql, function(err, result){
                callbackInner(err, result)
            })
        },
    ], callback);
};

exports.getBackOfficeReportService = function (community, date, column, interval, callback) {

    var table = column.split("$")[0]
    var returntype = column.split("$")[1]
    var datatype = column.split("$")[2]

    var db = "66xiaoqu_dwh"
    var count = "count(*)"
    var conditionList = []
    var tablejoins = ""
    var dateCondition = "dateCreated"

    /**
     * TABLE LOOKUP
     * Define table-specific information (db name, column name(s), table join(s))
     */
    if(table == "reply"){
        dateCondition = "dateReplied"
        var table2 = "question"
        var joinCondition = "on {0}.{1}._Questionid = {0}.{2}._id".format(db, table, table2)
        tablejoins += "inner join {0}.{2} as question {3}".format(db, table, table2, joinCondition)
    }
    if(table == "log_read"){
        dateCondition = "CASE when create_time > 10000000000 then from_unixtime(create_time / 1000) else from_unixtime(create_time) end "
    }


    /**
     * RETURN-TYPE LOOKUP
     * Define return type: count(*), count(distinct x)
     */
    if(returntype.indexOf("distinct") > -1)
        count = "count(distinct " + returntype.substring(returntype.indexOf("_")+1) + ")"

    /**
     * DATA-TYPE LOOKUP
     * Define sql conditions based on selected datatype
     */
    if(datatype.indexOf('users') > -1 && datatype.indexOf('question') == -1 && datatype.indexOf('replies') == -1){
        conditionList.push("(mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%' or mobilePhone is null)")
        if(datatype === 'users')
            count = 'floor(count(distinct _id) * 0.9)'
    }
    if(datatype.indexOf("vusers") > -1)
        conditionList.push("authenticationStatus='true'")
    if(datatype.indexOf("new") > -1){
        var tempdate = new Date(date)
        //var startDate = new Date(tempdate.setDate(tempdate.getDate() - interval)).format("yyyy-MM-dd hh:mm:ss")
        var startDate = new Date(tempdate.setDate(tempdate.getDate() - interval)).format("yyyy-MM-dd 23:59:59")
        conditionList.push(dateCondition + ">='" + startDate + "'")
    }
    if(datatype.indexOf("click_") > -1){
        db = "66xiaoqu_db"
        tablejoins = "as log left join 66xiaoqu_db.log_read_desc as logdesc on log.click_source = logdesc.click_source"
        conditionList.push("log.click_source='" + datatype.split("_")[1] + "'")
        conditionList.push(dateCondition + "<='" + date + "'")
    }


    // ================SQL=====================
    if(date != 'all')
        conditionList.push(dateCondition + "<='" + date + "'")

    if(community != 'all')
        conditionList.push("community='" + community + "'")
    else
        conditionList.push("community is not null")

    var condition = sqlConditionString(conditionList)

    var sql = 'select {0} count from {1}.{2} {3} {4}'.format(count, db, table, tablejoins, condition)

    /**
     * FOR SPECIAL CASES ONLY
     */

    if(table == 'dau' && datatype == 'activeusers'){

        var date = new Date(date).format('yyyy-MM-dd')

        var commCondition = ''
        if(community != 'all')
            commCondition = "and community ='" + community + "'"
        else
            commCondition = "and community is not null"
        var actionDateCondition = "and actionDate = '" + date + "'"
        if(interval == 7){
            var tempdate = new Date(date)
            var startDate = new Date(tempdate.setDate(tempdate.getDate() - interval)).format('yyyy-MM-dd')
            actionDateCondition = "and actionDate >'" + startDate + "' and actionDate <='" + date + "'"
        }

        sql = commentToString(function(){
            /*
             select coalesce(concat(round(( active.activeUsers / total.totalUsers * 100 ),2),'%'), '0.00%') count from
             (SELECT count(distinct _id) activeUsers
             FROM
             {0}.dau dau, 66xiaoqu_dwh.users users
             where dau.uid = users._id
             {1}
             {2}
             and (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%'
             or mobilePhone is null)
             ) as active,
             (
             select floor(count(distinct _id) * 0.9) totalUsers
             from 66xiaoqu_dwh.users
             where (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%'
             or mobilePhone is null)
             {1}
             and dateCreated <='{3}'
             ) as total
             */
        }).toString().format('66xiaoqu_rec', commCondition, actionDateCondition, new Date(date).format('yyyy-MM-dd 23:59:59'))
        //logger.info(interval)
        //logger.info(actionDateCondition)
        //logger.info(sql)
    }

    logger.info(sql)
    mysqlUtil.query(sql, function (err, result) {
        callback(err, result[0].count)
    })
}

exports.getBackOfficeRetentionByCommService = function (community, interval, callback) {
    var sql = commentToString(function(){
        /*
            select convert(batchDate, char) batchDate, convert(retentionPeriod, char) retentionPeriod, activeUsers, totalUsers, retentionRate from 66xiaoqu_rec.test_batchRetention where commId='{0}' and `interval`='{1}'
         */
    }).toString().format(community, interval)

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result)
    })
}


exports.getBackOfficeRetentionByCommAndBatchService = function (community, userBatchDate, interval, callback) {
    //var date = new Date(date).format('yyyy-MM-dd')

    var intervalPeriod = 'interval 7 day'
    var actionDateCondition = 'adddate(date(actionDate), INTERVAL - WEEKDAY(actionDate) day)'
    var actionDateFormat = actionDateCondition
    if(interval === 'monthly'){
        intervalPeriod = 'interval 1 month'
        actionDateCondition = 'month(actionDate)'
        actionDateFormat = "case when month(actionDate) < 10 then concat(year(actionDate), '-0', month(actionDate), '-{0}') else concat(year(actionDate), '-', month(actionDate), '-{0}') end".format(userBatchDate.split('-')[2])
    }

    var commCondition = ''
    if(community != 'all')
        commCondition = "and community ='" + community + "'"
    else
        commCondition = "and community is not null"

    var sql = commentToString(function(){
        /*
         select totalUsers, active, convert(startDate, char) startDate, active / totalUsers retentionRate from
         (
         SELECT count(distinct _id) active, {4} startDate
         FROM
         66xiaoqu_rec.dau dau, 66xiaoqu_dwh.users users
         where dau.uid = users._id
         and actionDate >= date_add('{0}', {2})
         and dateCreated >= '{0}' and dateCreated < date_add('{0}', {2})
         and (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%'
         or mobilePhone is null)
         {1}
         group by {3}
         ) as weekly,
         (
         select floor(count(distinct _id) * 0.9) totalUsers
         from
         66xiaoqu_dwh.users
         where dateCreated >= '{0}' and dateCreated < date_add('{0}', {2})
         and (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%'
         or mobilePhone is null)
         {1}
         ) as total;
         */
    }).toString().format(userBatchDate, commCondition, intervalPeriod, actionDateCondition, actionDateFormat)
    if(interval === 'monthly')
        logger.info(sql)

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getBackOfficeMarketingInviteListService = function (community, callback) {
    var commCondition = ''
    if(community != 'all')
        commCondition = "and u2.community ='" + community + "'"

    var sql = commentToString(function(){
        /*
         select u2.nickName inviter, u2._id 'inviterId', c2.communityName inviterComm, u1.nickName invitee, u1._id 'inviteeId', c1.communityName inviteeComm, convert(date(u1.dateCreated), char) 'dateCreated'
         from {0}.userdetail u1, {0}.userdetail u2, {0}.communitydetail c1, {0}.communitydetail c2
         where u1.community = c1._id
         and u1.invitedBy = u2._id
         and u2.community = c2.`_id`
         {1}
         order by u1.dateCreated desc;
         */
    }).toString().format('66xiaoqu_rec', commCondition)

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getBackOfficeMarketingInviteSentListService = function (community, callback) {
    var commCondition = ''
    if(community != 'all')
        commCondition = "and community ='" + community + "'"

    var sql = commentToString(function(){
        /*
         select communityName, nickName, icnt 'invitationSent', coalesce(acnt, 0) 'invitationClicked'
         from
         (
         select u._id, c._id cid , communityName, nickName, count(*) icnt
         from {0}.userdetail u, {0}.communitydetail c, {0}.invite_wechat_bind i
         where u.community = c._id
         and u._id = i.fromUserId
         {1}
         group by u._id
         ) u1
         left join
         (
         select invitedby, count(*) acnt
         from {0}.userdetail
         where invitedBy is not null
         group by invitedBy
         ) u2
         on u1._id = u2.invitedBy
         order by icnt desc;
         */
    }).toString().format('66xiaoqu_rec', commCondition)

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getBackOfficeClickSourceService = function (startDate, endDate, callback) {

    var startDateCondition = ""
    var endDateCondition = ""
    if(startDate != 'all')
        startDateCondition = "and CASE when create_time > 10000000000 then from_unixtime(create_time / 1000) else from_unixtime(create_time) end >='{0}'".format(startDate)
    if(endDate != 'all')
        endDateCondition = "and CASE when create_time > 10000000000 then from_unixtime(create_time / 1000) else from_unixtime(create_time) end <='{0}'".format(endDate)

    var sql = commentToString(function(){
        /*
         SELECT count(*) total, log.click_source, logdesc.click_desc
         FROM
         66xiaoqu_db.log_read as log left join 66xiaoqu_db.log_read_desc as logdesc on log.click_source = logdesc.click_source
         where log.click_source = logdesc.click_source
         {0} {1}
         group by log.click_source
         order by log.click_source
         */
    }).toString().format(startDateCondition, endDateCondition)
    logger.info(sql)

    mysqlUtil.query(sql, function (err, result) {
        callback(err, result)
    })
}